﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class DBConnection
    {
        String connStr = @"Data Source=.;Initial Catalog=DB_OnlineHelpDesk;User ID=sa;Password=123456";
        SqlConnection conn;
        SqlDataAdapter sda;
        SqlCommand cmd;
        DataTable dt;

        public void openConnection()
        {
            conn = new SqlConnection(connStr);
            if(conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        public void closeConnection()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
            }
        }

        public SqlConnection getConnection()
        {
            return conn;
        }

        public DataTable getData(string storeName)
        {
            conn = new SqlConnection(connStr);
            openConnection();
            cmd = new SqlCommand(storeName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            sda = new SqlDataAdapter(cmd);
            dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        public void UpdateData(String storeName, SqlParameter[] sqlpar)
        {
            conn = new SqlConnection(connStr);
            openConnection();
            cmd = new SqlCommand(storeName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < sqlpar.Length; i++)
                {
                    cmd.Parameters.Add(sqlpar[i]);
                }
            cmd.ExecuteNonQuery();
            closeConnection();   
        }
        public void UpdateData(String storeName)
        {
            conn = new SqlConnection(connStr);
            openConnection();
            cmd = new SqlCommand(storeName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            closeConnection();   
        }
        public DataSet getDataSet(String storeName)
        {
            DataSet ds = new DataSet();
            conn = new SqlConnection(connStr);
            openConnection();
            try
            {
                cmd = new SqlCommand(storeName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                sda = new SqlDataAdapter(cmd);
                sda.SelectCommand = cmd;
                sda.Fill(ds);
                closeConnection();
            }
            catch (SqlException ex)
            {
                string errorStr = ex.Message;
            }
            return ds;
        }
        public DataSet getDataSet(String storeName, SqlParameter[] sqlpar)
        {
            DataSet ds = new DataSet();
            conn = new SqlConnection(connStr);
            openConnection();
            try
            {
                cmd = new SqlCommand(storeName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < sqlpar.Length; i++)
                {
                    cmd.Parameters.Add(sqlpar[i]);
                }
                sda = new SqlDataAdapter(cmd);
                sda.SelectCommand = cmd;
                sda.Fill(ds);
                closeConnection();
            }
            catch (SqlException ex)
            {
                string errorStr = ex.Message;
            }
            return ds;
        }

        public DataTable getDataTable(string storeName, SqlParameter[] sqlpar)
        {
            dt = new DataTable();
            conn = new SqlConnection(connStr);
            openConnection();
            try
            {
                cmd = new SqlCommand(storeName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < sqlpar.Length; i++)
                {
                    cmd.Parameters.Add(sqlpar[i]);
                }
                sda = new SqlDataAdapter(cmd);
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                closeConnection();
            }
            catch (SqlException ex)
            {
                string errorStr = ex.Message;
            }
            return dt;
        }

    }
}
